Using Table Valued Functions in SQL Server 2005 To Implement a Spatial Data Library

This article explains how to add spatial search functions (point-near-point and point in polygon) to Microsoft SQL ServerTM 2005 using C# and table-valued functions. It is possible to use this library to add spatial search to your application without writing any special code. The library implements the public-domain C# Hierarchical Triangular Mesh (HTM) algorithms from Johns Hopkins University. That C# library is connected to SQL Server 2005 via a set of scalar-valued and table-valued functions. These functions act as a spatial index. Resources The article is illustrated by examples that can be downloaded from http://msdn.microsoft.com/sql/2005/. The sample package includes: • An 11 MB sample spatial database of United States cities and river-flow gauges. • The sample queries from the sql\testScript.sql article. • A Visual Studio 2005 project, \htm.sln, with all the SQL and C# code. • A paper, doc\Table_Valued_Functions.doc. • An article, doc\HtmCsharp.doc, that provides a manual page for each routine. • An article, doc\HTM.doc, that explains the Hierarchical Triangular Mesh algorithms in detail. • An article, doc\There_Goes_the_Neighborhood.doc, which explains how the HTM algorithms are used in Astronomy. This article also explains two other approaches: zones for batch-oriented point-to-point and point-area comparisons, and regions for doing Boolean algebra on areas. Public domain implementations of those approaches implemented for SQL Server are used in the SkyServer, a popular Astronomy website for the Sloan Digital Sky Survey (http://SkyServer.SDSS.org/ and by several other astronomy data servers.